﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.EffiProz;

namespace EffiProz.Test
{
    /// <summary>
    /// Summary description for TestLobs
    /// </summary>
    [TestClass]
    public class TestLobs
    {
        private string connString = "Connection Type=Memory ; Initial Catalog=LobsTestDB; User=sa; Password=;";

        public TestLobs()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void BlobA()
        {
            using (EfzConnection conn = new EfzConnection(connString))
            {
                String ddl0 = "DROP TABLE BLOBTEST IF EXISTS";
                String ddl1 = "CREATE TABLE BLOBTEST(ID IDENTITY, BLOBFIELD BLOB(1000))";

                conn.Open();
                DbCommand command = conn.CreateCommand();

                command.CommandText = ddl0;
                command.ExecuteNonQuery();

                command.CommandText = ddl1;
                command.ExecuteNonQuery();

                String dml0 = "insert into blobtest(blobfield) values(@blobvalue)";
                String dql0 = "select * from blobtest;";

                command.CommandText = dml0;
                command.Prepare();

                byte[] data = new byte[] {
                 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
                };

                DbParameter blobParameter = command.CreateParameter();
                blobParameter.Size = data.Length;
                blobParameter.Value = data;
                blobParameter.Direction = ParameterDirection.Input;
                blobParameter.ParameterName = "@blobvalue";

                command.Parameters.Add(blobParameter);

                int count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);
               

                 data[4] = 50;
                 blobParameter.Value = data;
                 count = command.ExecuteNonQuery();
                 Assert.AreEqual(1, count);             


                 DbCommand commandQuery = conn.CreateCommand();
                 commandQuery.CommandText = dql0;

                 DbDataReader reader = commandQuery.ExecuteReader();

                 Assert.IsTrue(reader.Read());
                 Assert.IsFalse(reader.IsDBNull(1));
                 byte[] data1 = new byte[10];
                 long data1Len = reader.GetBytes(1,0,data1,0,10);
                 Assert.AreEqual(10,data1Len);
                 Assert.AreEqual(5,data1[4]);


                 Assert.IsTrue(reader.Read());
                 Assert.IsFalse(reader.IsDBNull(1));
                 byte[] data2 = new byte[10];
                 long data2Len = reader.GetBytes(1, 0, data2, 0, 10);
                 Assert.AreEqual(10, data2Len);
                 Assert.AreEqual(50, data2[4]);


                 Assert.IsFalse(reader.Read());             
            }
        }

        [TestMethod]
        public void ClobA()
        {
            using (EfzConnection conn = new EfzConnection(connString))
            {
                String ddl0 = "DROP TABLE CLOBTEST IF EXISTS";
                String ddl1 = "CREATE TABLE CLOBTEST(ID IDENTITY, CLOBFIELD CLOB(1000))";
                
                conn.Open();
                DbCommand command = conn.CreateCommand();

                command.CommandText = ddl0;
                command.ExecuteNonQuery();

                command.CommandText = ddl1;
                command.ExecuteNonQuery();

                String dml0 = "insert into clobtest(clobfield) values(@clobfield)";
                String dql0 = "select * from clobtest;";

                command.CommandText = dml0;
                command.Prepare();

                String data = "Testing clob insert and select ops";

                DbParameter clobParameter = command.CreateParameter();
                clobParameter.Size = data.Length;
                clobParameter.Value = data.ToCharArray();
                clobParameter.Direction = ParameterDirection.Input;
                clobParameter.ParameterName = "@clobfield";

                command.Parameters.Add(clobParameter);

                int count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);


                data = data.Replace("insert", "INSERT");
                clobParameter.Value = data.ToCharArray();
                count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);


                DbCommand commandQuery = conn.CreateCommand();
                commandQuery.CommandText = dql0;

                DbDataReader reader = commandQuery.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.IsFalse(reader.IsDBNull(1));
                char[] data1 = new char[data.Length];
                long data1Len = reader.GetChars(1, 0, data1, 0, data1.Length);
                Assert.AreEqual(data.Length, data1Len);
                int data1Index = (new string(data1)).IndexOf("insert");
                Assert.IsTrue(data1Index > 1);


                Assert.IsTrue(reader.Read());
                Assert.IsFalse(reader.IsDBNull(1));
                char[] data2 = new char[data.Length];
                long data2Len = reader.GetChars(1, 0, data2, 0, data2.Length);
                Assert.AreEqual(data.Length, data2Len);
                int data2Index = (new string(data2)).IndexOf("INSERT");
                Assert.IsTrue(data2Index > 1);

                Assert.AreEqual(data1Index, data2Index);

                Assert.IsFalse(reader.Read());
            }
        }

        [TestMethod]
        public void ClobB()
        {
            using (EfzConnection conn = new EfzConnection(connString))
            {
                String ddl0 = "DROP TABLE CLOBTESTB IF EXISTS";
                String ddl1 = "CREATE TABLE CLOBTESTB(ID IDENTITY, V VARCHAR(10), I INT, CLOBFIELD CLOB(1000))";

                conn.Open();
                DbCommand command = conn.CreateCommand();

                command.CommandText = ddl0;
                command.ExecuteNonQuery();

                command.CommandText = ddl1;
                command.ExecuteNonQuery();

                String dml0 = "insert into clobtestb values(default, @v, @i, @clobfield)";
                String dql0 = "select * from clobtestb;";

                command.CommandText = dml0;
                command.Prepare();

                String data = "Testing clob insert and select ops";

                DbParameter vParameter = command.CreateParameter();
                vParameter.Value = "test";            
                vParameter.ParameterName = "@v";
                command.Parameters.Add(vParameter);

                DbParameter iParameter = command.CreateParameter();
                iParameter.Value = null;
                iParameter.ParameterName = "@i";
                command.Parameters.Add(iParameter);

                DbParameter clobParameter = command.CreateParameter();
                clobParameter.Size = data.Length;
                clobParameter.Value = new System.IO.StringReader(data);        
                clobParameter.ParameterName = "@clobfield";
                command.Parameters.Add(clobParameter);

                int count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);


                data = data.Replace("insert", "INSERT");
                clobParameter.Value = new System.IO.StringReader(data);
                iParameter.Value = 5;
                count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);


                DbCommand commandQuery = conn.CreateCommand();
                commandQuery.CommandText = dql0;

                DbDataReader reader = commandQuery.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual("test",reader.GetString(1));
                Assert.IsTrue( reader.IsDBNull(2));
                Assert.IsFalse(reader.IsDBNull(3));
                char[] data1 = new char[data.Length];
                long data1Len = reader.GetChars(3, 0, data1, 0, data1.Length);
                Assert.AreEqual(data.Length, data1Len);
                int data1Index = (new string(data1)).IndexOf("insert");
                Assert.IsTrue(data1Index > 1);


                Assert.IsTrue(reader.Read());
                 Assert.AreEqual("test",reader.GetString(1));
                 Assert.AreEqual(5, reader.GetInt32(2));
                Assert.IsFalse(reader.IsDBNull(3));
                char[] data2 = new char[data.Length];
                long data2Len = reader.GetChars(3, 0, data2, 0, data2.Length);
                Assert.AreEqual(data.Length, data2Len);
                int data2Index = (new string(data2)).IndexOf("INSERT");
                Assert.IsTrue(data2Index > 1);

                Assert.AreEqual(data1Index, data2Index);

                Assert.IsFalse(reader.Read());
            }
        }

        [TestMethod]
        public void BlobB()
        {         
            byte[] baR1 = new byte[] {
            (byte) 0xF1, (byte) 0xF2, (byte) 0xF3, (byte) 0xF4, (byte) 0xF5,
            (byte) 0xF6, (byte) 0xF7, (byte) 0xF8, (byte) 0xF9, (byte) 0xFA
        };
            byte[] baR2 = new byte[] {
            (byte) 0xE1, (byte) 0xE2, (byte) 0xE3, (byte) 0xE4, (byte) 0xE5,
            (byte) 0xE6, (byte) 0xE7, (byte) 0xE8, (byte) 0xE9, (byte) 0xEA
        };


            using (EfzConnection conn = new EfzConnection(connString))
            {                           
                conn.Open();
                conn.AutoCommit = false;

                DbCommand command = conn.CreateCommand();

                command.CommandText = "CREATE TABLE blo (id INTEGER, b blob( 100))";
                command.ExecuteNonQuery();              

                command.CommandText = "INSERT INTO blo(id, b) values(@id, @b)";
                command.Prepare();

                DbParameter idParameter = command.CreateParameter();                
                idParameter.Value = 1;
                idParameter.Direction = ParameterDirection.Input;
                idParameter.ParameterName = "@id";
                command.Parameters.Add(idParameter);

                DbParameter blobParameter = command.CreateParameter();
                blobParameter.Size = baR1.Length;
                blobParameter.Value = baR1;
                blobParameter.Direction = ParameterDirection.Input;
                blobParameter.ParameterName = "@b";
                command.Parameters.Add(blobParameter);

                int count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);

                idParameter.Value = 2;
                blobParameter.Size = baR2.Length;
                blobParameter.Value = baR2;

                count = command.ExecuteNonQuery();
                Assert.AreEqual(1, count);


                DbCommand commandQuery = conn.CreateCommand();
                commandQuery.CommandText = "SELECT b FROM blo WHERE id = 1";

                DbDataReader reader = commandQuery.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.IsFalse(reader.IsDBNull(0));
                Assert.AreEqual(0, reader.GetOrdinal("b"));
            

                byte[] data1 = new byte[baR1.Length];
                long data1Len = reader.GetBytes(0, 0, data1, 0, baR1.Length);
                Assert.AreEqual(baR1.Length, data1Len);
                for (int i = 0; i < baR1.Length; i++)
                    Assert.AreEqual(baR1[i], data1[i]);

                reader.Close();
             
                commandQuery.CommandText = "SELECT b FROM blo WHERE id = 2";
                reader = commandQuery.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.IsFalse(reader.IsDBNull(0));
                Assert.AreEqual(0, reader.GetOrdinal("b"));


                byte[] data2 = new byte[baR2.Length];
                long data2Len = reader.GetBytes(0, 0, data2, 0, baR2.Length);
                Assert.AreEqual(baR2.Length, data2Len);
                for (int i = 0; i < baR2.Length; i++)
                    Assert.AreEqual(baR2[i], data2[i]);

            }
        }
    }
}
